import sys
import os
sys.path.append(os.getcwd() + '/dao')
sys.path.append(os.getcwd() + '/entity')
import urllib.request as request
from urllib import parse
import json

from db import POOL



def save_province(province):
    """
        保存省数据
    :return:
    """
    conn = POOL.connection()
    cursor = conn.cursor()
    try:
        sql = "INSERT INTO `aps_province_dict` (`name`, `code`) VALUES ('%s', '%s')" % \
              (province['name'], province['adcode'])
        cursor.execute(sql)
        id = int(cursor.lastrowid)
        print(id, province['name'], province['adcode'])
        return id
    except BaseException as e:
        print(e)
    finally:
        conn.commit()
        cursor.close()
        conn.close()

    return None


def save_cities(cities_list, province_id):
    """
        保存省数据
    :return:
    """
    conn = POOL.connection()
    cursor = conn.cursor()
    try:
        for city in cities_list:
            sql = "INSERT INTO `aps_city_dict` (`name`, `code`,`province_id`) VALUES ('%s', '%s',%s)" % \
                  (city['name'], city['adcode'], province_id)
            cursor.execute(sql)
            id = int(cursor.lastrowid)
            print(id, city['name'], city['adcode'])
    except BaseException as e:
        print(e)
    finally:
        conn.commit()
        cursor.close()
        conn.close()


def has_special_city():
    """
     没有直辖市的城市新增
    :return:
    """
    province_URL = 'http://restapi.amap.com/v3/config/district?key=476a9d7f5bb059b8b04c2779693d04f0'
    down_file_content = (request.urlopen(province_URL).read()).decode("utf-8")

    province_list = json.loads(down_file_content)['districts'][0]['districts']

    print(str(len(province_list)) + "省")

    province_city_map = {}

    for province in province_list:

        if province['level'] == 'province':
            province_code = province['adcode']
            province_name = province['name']

            cities_url = province_URL
            values = {'keywords': province_name}
            data = parse.urlencode(values).encode('utf-8')
            down_file_content = (request.urlopen(cities_url,data).read()).decode("utf-8")
            cities_list = json.loads(down_file_content)['districts'][0]['districts']

            province_city_map[province_code] = cities_list

    for k, v in province_city_map.items():
        for province in province_list:
            if province['adcode'] == k:
                id = save_province(province)
                save_cities(v, id)
                print(k, province['name'], v)

    print('处理完成')


def has_special_city_1():
    province_city_URL = 'http://restapi.amap.com/v3/config/district?key=476a9d7f5bb059b8b04c2779693d04f0&subdistrict=2'

    down_file_content = (request.urlopen(province_city_URL).read()).decode("utf-8")

    province_list = json.loads(down_file_content)['districts'][0]['districts']

    print(len(province_list))

    for province in province_list:
        cities = province['districts']
        id = save_province(province)
        save_cities(cities, id)
        print(province['name'], cities)


def aps_city_read(table_name):
    """
     查询city表
    :param table_name:
    :return:
    """

    conn = POOL.connection()
    try:
        cursor = conn.cursor()
        sql = "select * from `%s` " % table_name
        cursor.execute(sql)
        results = cursor.fetchall()
        cities = []
        for row in results:

            if 'aps_city_dict' == table_name:
                city = {
                    "name" : row[2]
                }
                cities.append(city)

            elif 'aps_city' == table_name:
                city = {
                    "code" : row[1],
                    "name" : row[4]
                }
                cities.append(city)

            else:
                raise RuntimeError('table_name is error')
        return cities
    except BaseException as e:
        print(e)
        return e


def handle_code(x, name_code_map):
    x['code'] = name_code_map.get(x['name'])
    return x


def update_city(handled_city_list):
    sql_array =[]
    for handled_city in handled_city_list:
        sql = "update `aps_city_dict` set code = '%s' where `name` = '%s'" % (handled_city['code'], handled_city['name'])
        sql_array.append(sql)

    sqls = str.join(";", sql_array)
    print(sqls)
    conn = POOL.connection()
    cursor = conn.cursor()
    try:

        for sql in sql_array:
            cursor.execute(sql)
            id = int(cursor.lastrowid)
            print(id)

    except BaseException as e:
        print(e)
    finally:
        conn.commit()
        cursor.close()
        conn.close()


if __name__ == '__main__':

    aps_city_dicts = aps_city_read('aps_city_dict')
    aps_cities = aps_city_read('aps_city')

    name_code_map = {}
    for city in aps_cities:
        name_code_map[city['name']] = city['code']

    handled_city_list = list(filter(lambda x: x['code'] is not None, map(lambda x: handle_code(x, name_code_map), aps_city_dicts)))
    print(len(handled_city_list))
    print(handled_city_list)

    retD = list(set([x['name'] for x in aps_cities]).difference(set([x['name'] for x in handled_city_list])))
    print("差集", retD)

    update_city(handled_city_list)









